using System;
using System.Collections.Generic;
using System.Text;
using System.Resources;
using System.IO;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using SQLString.Dialogs;
using SQLString.AppResource;

namespace SQLString.Classes
{
	#region Database Class, handles Creation, population and validation of the Database


	public class DatabaseObjects
	{
		public DatabaseObjects( fSQLStringSettings _frm, SQLResource _res )
		{
			val = _frm;
			RES = _res;
			FUNC = new CommonFuntions();
		}

		fSQLStringSettings val=null;
		SQLResource RES=null;
		CommonFuntions FUNC=null;
		#region Properties
		
		
		string szObjectName = string.Empty;
		int iObjectType = 0;
		string szDBList = "('T_DATA_TYPES','T_LANG','T_SQLSTRING','T_SQLSTRING_CODE','T_SQLSTRING_PROJECT','T_SQLSTRING_PROJECT_TYPES')";
		string[] szTableObjects = new string[]	//	This is the list of valid objects, if not all found then exception is raised.
													{ 
														"T_SQLSTRING_CODE", 
														"T_SQLSTRING", 
														"T_SQLSTRING_PROJECT",
														"T_DATA_TYPES",
														"T_LANG", 
														"T_SQLSTRING_PROJECT_TYPES"
													};
		string[] szVerifiedTableObjects = new string[] { "", "", "", "", "", "" };
		string[] szretTableObjects = new string[] { "", "", "", "", "", "" };
		string[] szMissingTableObj = new string[] { "", "", "", "", "", "" };




		string[] szConstraintObjects = new string[] { 
														
														"FK_T_LANG_T_DATA_TYPES",
														"FK_T_SQLSTRING_PROJECT_T_SQLSTRING_PROJECT_TYPES", 
														"FK_T_SQLSTRING_CODE_T_LANG1", 
														"FK_T_SQLSTRING_T_SQLSTRING_PROJECT", 
														"FK_T_SQLSTRING_CODE_T_SQLSTRING"
													};


		string[] szSQLDropConstraints = new string[] 
													{	
														"ALTER TABLE [dbo].[T_LANG] DROP CONSTRAINT FK_T_LANG_T_DATA_TYPES",
														"ALTER TABLE [dbo].[T_SQLSTRING_CODE] DROP CONSTRAINT FK_T_SQLSTRING_CODE_T_LANG1",
														"ALTER TABLE [dbo].[T_SQLSTRING_PROJECT] DROP CONSTRAINT FK_T_SQLSTRING_PROJECT_T_SQLSTRING_PROJECT_TYPES", 
														"ALTER TABLE [dbo].[T_SQLSTRING] DROP CONSTRAINT FK_T_SQLSTRING_T_SQLSTRING_PROJECT",
														"ALTER TABLE [dbo].[T_SQLSTRING_CODE] DROP CONSTRAINT FK_T_SQLSTRING_CODE_T_SQLSTRING"
													};
		
		string[] szSQLDropTables = new string[] 
													{	
														"DROP TABLE [DBO].[T_SQLSTRING_CODE]",
														"DROP TABLE [DBO].[T_SQLSTRING]",
														"DROP TABLE [DBO].[T_SQLSTRING_PROJECT]",
														"DROP TABLE [dbo].[T_DATA_TYPES]",
														"DROP TABLE [DBO].[T_LANG]",
														"DROP TABLE [DBO].[T_SQLSTRING_PROJECT_TYPES]"
													};

		public string DBOList
		{
			get
			{
				return szDBList.Trim();
			}
			set
			{
				szDBList = value.Trim();
			}
		}
		public string DBOName
		{
			get
			{
				return szObjectName.Trim();
			}
			set
			{
				szObjectName = value.Trim();
			}
		}

		public int DBObjectType
		{
			get
			{
				return iObjectType;
			}
			set
			{
				iObjectType = value;
			}
		}

		public string[] DBOConstraintsbjectCollection
		{
			get
			{
				return szConstraintObjects;
			}
			set
			{
				szConstraintObjects = value;
			}
		}

		public string[] DBOTableObjectCollection
		{
			get
			{
				return szTableObjects;
			}
			set
			{
				szTableObjects = value;
			}
		}

		public string[] DBOVerifiedTableObjectCollection
		{
			get
			{
				return szVerifiedTableObjects;
			}
			set
			{
				szVerifiedTableObjects = value;
			}
		}

		public string[] DBORetrievedTableObjectCollection
		{
			get
			{
				return szretTableObjects;
			}
			set
			{
				szretTableObjects = value;
			}
		}
		public string[] DBMissingObjects
		{
			get
			{
				return szMissingTableObj;
			}
			set
			{
				szMissingTableObj = value;
			}
		}
		#endregion

		#region BOSS Mehtods!!  These guys stand around and lead the others who do all the wok and they take the credit!!!  Just like real world!


		/// <summary>
		/// Rebuilds the DB - overrites existing DB.  Refer to "EVENT:	User Initiated DB Creation" in the Design Addendum.
		/// This is the execution if the application determines there are pre-existing DB objects when they select "Create DB".
		/// </summary>
		/// <returns></returns>
		/// **********************************************************************************************************
		/// *	JBH Software Systems
		/// *	Project:	
		/// *	Copyright (C) 2008- 
		/// *
		/// *	Source Code is Property of 
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.6/23/2008
		/// *
		/// *********************************************************************************************************
		public bool RebuildDB()
		{
			SqlConnection dbConn = new SqlConnection(val.Connection);
			try
			{
				//	check for existence and drop any tables/constraints
				int iRetVal = 0;
				int iCntr = 0;
				
				string szSQL = string.Empty;
				bool bErrorCond = false;
				SqlCommand sqlCMD = new SqlCommand();
				sqlCMD.Connection = dbConn;
				dbConn.Open();
				if (DropConstraints())	// Dropping Constraints
				{
					if (DropTables())		// Dropping tables
					{
						if (CreateDataBase())
						{
							return true;
						}
						else
							return false;
					}
					else
						return false;
				}else return false;
			}
			catch (SQLString.Classes.SQLManagementException ex)
			{
				Monitor.sErrMsg("The Table Object Creation Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				Monitor.sErrMsg("The Table Object Creation Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (Exception ex)
			{
				Monitor.sErrMsg("Application Error: " + ex.Message);
				return false;
			}
			finally
			{
				dbConn.Dispose();
			}
		}

		#endregion

		#region Database Rebuild - Reset Methods, Drop Constraints and Tables


		/// <summary>
		/// Only execute if we are building the db again
		/// </summary>
		/// <returns></returns>
		/// **********************************************************************************************************
		/// *	JBH Software Systems
		/// *	Project:	
		/// *	Copyright (C) 2008- 
		/// *
		/// *	Source Code is Property of 
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.6/22/2008
		/// *
		/// *********************************************************************************************************
		private bool DropConstraints()
		{
			SqlConnection dbConn = new SqlConnection(val.Connection);
			try
			{
				string[] szErrorDropping = new string[] { "","","","","","" };
				//	Method assumes constraints exist, it will only delete them
				string szSQL = string.Empty;
				int iCntr = 0;
				int iRetVal = 0;
				bool bErrorCond = false;
				SqlCommand sqlCMD = new SqlCommand();
				sqlCMD.Connection = dbConn;
				dbConn.Open();
				foreach (string szConstraintName in szSQLDropConstraints)
				{
					szSQL = szConstraintName.Trim();
					sqlCMD.CommandText = szSQL;
					iRetVal = sqlCMD.ExecuteNonQuery();
					if (iRetVal != -1)
					{
						szErrorDropping[iCntr] = szConstraintName.Trim();	// capture the offending constraint and keep going
						bErrorCond = true;
					}
					sqlCMD.Dispose();
					iCntr++;

				}
				if (bErrorCond)	//	If an error occurs it may not be fatal to the overall procedure of creating a new DB.  will display the offending issue and continue.
				{
					FUNC.DisplayCommonList(szErrorDropping, "Constraint(s) Erroring During the Drop Phase");
					Monitor.sMsg("Continuing to Create Database.  Please note the Error.", MessageBoxIcon.Information);
				}
				return true;
			}
			catch (SQLString.Classes.SQLManagementException ex)
			{
				Monitor.sErrMsg("Database Object Delete Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				Monitor.sErrMsg("Database Object Delete Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (Exception ex)
			{
				Monitor.sErrMsg("Application Error: " + ex.Message);
				return false;
			}
			finally
			{
				dbConn.Dispose();
			}
		}


		/// <summary>
		/// Drops the tables.
		/// </summary>
		/// <returns></returns>
		/// **********************************************************************************************************
		/// *	JBH Software Systems
		/// *	Project:	
		/// *	Copyright (C) 2008- 
		/// *
		/// *	Source Code is Property of 
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.6/23/2008
		/// *
		/// *********************************************************************************************************
		private bool DropTables()
		{
			SqlConnection dbConn = new SqlConnection(val.Connection);
			try
			{
				string[] szErrorDropping = new string[] { "", "", "", "", "", "" };
				//	Method assumes tables exist, it will only delete them
				string szSQL = string.Empty;
				int iCntr = 0;
				int iRetVal = 0;
				bool bErrorCond = false;
				SqlCommand sqlCMD = new SqlCommand();
				sqlCMD.Connection = dbConn;
				dbConn.Open();
				foreach (string szTableName in szSQLDropTables)
				{
					szSQL = szTableName.Trim();
					sqlCMD.CommandText = szSQL;
					iRetVal = sqlCMD.ExecuteNonQuery();
					if (iRetVal != -1)
					{
						szErrorDropping[iCntr] = szTableName.Trim();	// capture the offending constraint and keep going
						bErrorCond = true;
					}
					sqlCMD.Dispose();
					iCntr++;

				}
				if (bErrorCond)	//	If an error occurs it may not be fatal to the overall procedure of creating a new DB.  will display the offending issue and continue.
				{
					FUNC.DisplayCommonList(szErrorDropping, "Table(s) Erroring During the Drop Phase");
					Monitor.sMsg("Continuing to Create Database.  Please note the Error.", MessageBoxIcon.Information);
				}
				return true;
			}
			catch (SQLString.Classes.SQLManagementException ex)
			{
				Monitor.sErrMsg("Database Object Delete Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				Monitor.sErrMsg("Database Object Delete Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (Exception ex)
			{
				Monitor.sErrMsg("Application Error: " + ex.Message);
				return false;
			}
			finally
			{
				dbConn.Dispose();
			}
		}

		#endregion

		#region Database Instantiation Methods- Create Objects, Fill em up!!!
		/// <summary>
		/// Initializes the database.
		/// Switching to transactional processing for this method.
		/// </summary>
		/// <returns></returns>
		/// **********************************************************************************************************
		/// *	JBH Software Systems
		/// *	Project:	
		/// *	Copyright (C) 2008- 
		/// *
		/// *	Source Code is Property of 
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.6/24/2008
		/// *
		/// *********************************************************************************************************
		private bool InitializeDatabase()
		{
			using (SqlConnection dbConn = new SqlConnection(val.Connection))
			{
				SqlTransaction sqlTRAN=null;	// Transaction allocation
				SqlCommand sqlCMD=null;
				try
				{
					string[] szErrorCreatingLANG = new string[] { "", "", "", "", "", "", "", ""};
					string[] szErrorCreatingPROJ = new string[] { "", "", "", "", "", "", "", "", "", ""};
					string[] szErrorCreatingTYPES = new string[] { "", "", "", ""};
					string szSQL = string.Empty;
					int iCntrL = 0;
					int iCntrP = 0;
					int iCntrD = 0;
					int iRetVal = 0;
					bool bErrorCond = false;
					bool bLang = false;
					bool bProj = false;
					bool bDataType = false;
					dbConn.Open();														// Opening DBConnection
					sqlCMD = dbConn.CreateCommand();									// Creating the SQLCommand Object
					sqlTRAN = dbConn.BeginTransaction("DBINITIALIZATION");				//	Instantiating the transaction and naming it.
					sqlCMD.Connection = dbConn;
					sqlCMD.Transaction = sqlTRAN;										//	Associating the transaction object to the sqlcommand object
					iRetVal = 0;
					foreach (string szLanguage in RES.SEEDLangData)						// Doing transaction for the DDL to insert data into T_LANG
					{
						szSQL = szLanguage.Trim();
						sqlCMD.CommandText = szSQL;
						iRetVal = sqlCMD.ExecuteNonQuery();
						if (iRetVal != 1 && iCntrL< 7)								//	7 is the number which will be returned by the last DDL when it does the UPDATE
						{
							szErrorCreatingLANG[iCntrL] = szLanguage.Trim();
							bErrorCond = true;
							bLang = true;
						}
						sqlCMD.Dispose();
						iCntrL++;
					}
					iRetVal = 0;
					foreach (string szProjData in RES.SEEDProjectTypeData)				// Doing transaction for the DDL to insert data into T_PROJECT_TYPES
					{
						szSQL = szProjData.Trim();
						sqlCMD.CommandText = szSQL;
						iRetVal = sqlCMD.ExecuteNonQuery();
						if (iRetVal != 1)
						{
							szErrorCreatingPROJ[iCntrP] = szProjData.Trim();
							bErrorCond = true;
							bProj = true;
						}
						sqlCMD.Dispose();
						iCntrP++;
					}
					iRetVal = 0;
					foreach (string szDataType in RES.SEEDDataTypes)				// Doing transaction for the DDL to insert data into T_PROJECT_TYPES
					{
						szSQL = szDataType.Trim();
						sqlCMD.CommandText = szSQL;
						iRetVal = sqlCMD.ExecuteNonQuery();
						if (iRetVal != 1 && iCntrL < 4)
						{
							szErrorCreatingPROJ[iCntrD] = szDataType.Trim();
							bErrorCond = true;
							bDataType = true;
						}
						sqlCMD.Dispose();
						iCntrD++;
					}
					//	 Perform final step -commit even if we have errors.  Just let the user see them first in case we have an exception condition on the commit.
					if (bErrorCond)	//	If an error occurs it may not be fatal to the overall procedure of creating a new DB.  will display the offending issue and continue.
					{
						if (bLang)
						{
							FUNC.DisplayCommonList(szErrorCreatingLANG, "Initialization Data for T_LANG Which Errored in Data Init Phase of Database Creation");
							Monitor.sMsg("Database Create Process did not complete.  Please check Permissions and re-execute.", MessageBoxIcon.Information);
						}
						else if (bProj)
						{
							FUNC.DisplayCommonList(szErrorCreatingPROJ, "Initialization Data for T_PROJECT_TYPES Which Errored in Data Init Phase of Database Creation");
							Monitor.sMsg("Database Create Process did not complete.  Please check Permissions and re-execute.", MessageBoxIcon.Information);
						}
						else if (bDataType)
						{
							FUNC.DisplayCommonList(szErrorCreatingPROJ, "Initialization Data for T_DATA_TYPES Which Errored in Data Init Phase of Database Creation");
							Monitor.sMsg("Database Create Process did not complete.  Please check Permissions and re-execute.", MessageBoxIcon.Information);
						}
					}
					sqlTRAN.Commit();
					return true;			//	If good, do the PK's and FK's
				}
				catch (Exception ex)
				{
					Monitor.sErrMsg("Database Object Insert Error.  ATTEMPTING TO ROLLBACK TRANSACTION.  \nERROR DETAIL:\n" + ex.Message);
					try
					{
						sqlTRAN.Rollback("DBINITIALIZATION");
						return false;
					}
					catch (Exception ex_tran_rollback)
					{
						Monitor.sErrMsg("Database Transaction Error.  ROLLBACK EXCEPTION. \nERROR DETAIL:\n" + ex_tran_rollback.Message);
						return false;
					}
				}
				finally
				{
					sqlTRAN.Dispose();
					dbConn.Dispose();
				}
			}
		}

		private bool CreateKEYS()
		{
			SqlConnection dbConn = new SqlConnection(val.Connection);
			try
			{
				string[] szErrorCreatingPK = new string[] { "", "", "", "", "", "" };
				string[] szErrorCreatingFK = new string[] { "", "", ""};
				string szSQL = string.Empty;
				int iCntrPK = 0;
				int iCntrFK = 0;
				int iRetVal = 0;
				bool bErrorCond = false;
				bool bPK = false;
				bool bFK = false;
				SqlCommand sqlCMD = new SqlCommand();
				sqlCMD.Connection = dbConn;
				dbConn.Open();
				foreach (string szPKDDL in RES.PrimaryKeys)		// Executing the DDL ---- place into own method
				{
					szSQL = szPKDDL.Trim();
					sqlCMD.CommandText = szSQL;
					iRetVal = sqlCMD.ExecuteNonQuery();
					if (iRetVal != -1)
					{
						szErrorCreatingPK[iCntrPK] = szPKDDL.Trim();
						bErrorCond = true;
						bPK = true;
					}
					sqlCMD.Dispose();
					iCntrPK++;
				}
				iRetVal = 0;
				foreach (string szFKDDL in RES.ForeignKeys)		// Executing the DDL ---- place into own method
				{
					szSQL = szFKDDL.Trim();
					sqlCMD.CommandText = szSQL;
					iRetVal = sqlCMD.ExecuteNonQuery();
					if (iRetVal != -1)
					{
						szErrorCreatingFK[iCntrFK] = szFKDDL.Trim();
						bErrorCond = true;
						bFK = true;
					}
					sqlCMD.Dispose();
					iCntrFK++;
				}
				if (bErrorCond)	//	If an error occurs it may not be fatal to the overall procedure of creating a new DB.  will display the offending issue and continue.
				{
					if (bPK)
					{
						FUNC.DisplayCommonList(szErrorCreatingPK, "Primary Key Creation Phase");
						Monitor.sMsg("Database Create Process did not complete.  Please check Permissions and re-execute.", MessageBoxIcon.Information);
						return false;
					}
					else if (bFK)
					{
						FUNC.DisplayCommonList(szErrorCreatingFK, "Primary Key Creation Phase");
						Monitor.sMsg("Database Create Process did not complete.  Please check Permissions and re-execute.", MessageBoxIcon.Information);
						return false;
					}
				}
				return true;
			}
			catch (SQLString.Classes.SQLManagementException ex)
			{
				Monitor.sErrMsg("Database Object Creation Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				Monitor.sErrMsg("Database Object Creation Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (Exception ex)
			{
				Monitor.sErrMsg("Application Error: " + ex.Message);
				return false;
			}
			finally
			{
				dbConn.Dispose();
			}
		}
		/// <summary>
		/// Creates the data base.
		/// 
		/// In the FUNC.DisplayCommonList -  the tables names are wider than the ListBox.
		/// 1.) Make the dialog larger.
		/// 2.) Set HorizontalScroll = true persistently
		/// 3.) Set HOROZONTAL SCROLL == true  when the text in is wider than the listbox, but the text width has to be 
		///				translated to pixels, because the listbox is in screen res.  mode.  
		///				gdi.MeasureString() ?  Not an acccurate method....
		///				The other equation from that Swiss dude?
		/// </summary>
		/// <returns></returns>
		/// **********************************************************************************************************
		/// *	JBH Software Systems
		/// *	Project:	
		/// *	Copyright (C) 2008- 
		/// *
		/// *	Source Code is Property of 
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.6/24/2008
		/// *
		/// *********************************************************************************************************
		public bool CreateDataBase()
		{
			SqlConnection dbConn = new SqlConnection(val.Connection);
			try
			{
				string[] szErrorCreating = new string[] { "", "", "", "", "", "" };
				string szSQL = string.Empty;
				int iCntr = 0;
				int iRetVal = 0;
				bool bErrorCond = false;
				SqlCommand sqlCMD = new SqlCommand();
				sqlCMD.Connection = dbConn;
				dbConn.Open();
				if (RES.LoadDBSchema())		//	Loading the table creation DDL
				{
					foreach (string szTableName in RES.TableSchema)		// Executing the DDL ---- place into own method
					{
						szSQL = szTableName.Trim();
						sqlCMD.CommandText = szSQL;
						iRetVal = sqlCMD.ExecuteNonQuery();
						if (iRetVal != -1)
						{
							szErrorCreating[iCntr] = szTableName.Trim();
							bErrorCond = true;
						}
						sqlCMD.Dispose();
						iCntr++;
					}
					if (bErrorCond)	//	If an error occurs it may not be fatal to the overall procedure of creating a new DB.  will display the offending issue and continue.
					{
						FUNC.DisplayCommonList(szErrorCreating, "Table(s) Erroring Creation Phase");
						Monitor.sMsg("Database Create Process did not complete.  Please check Permissions and re-execute.", MessageBoxIcon.Information);
						return false;
					}
					else if (bErrorCond == false)		//	 Perform final step - run seed data population scripts
					{
						if (InitializeDatabase())
						{
							if (CreateKEYS())
							{
								return true;		//DONE
							}
							else
								return false;
						}
						else
							return false;
					}
				}

				return true;
			}
			catch (SQLString.Classes.SQLManagementException ex)
			{
				Monitor.sErrMsg("Database Object Creation Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				Monitor.sErrMsg("Database Object Creation Error.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (Exception ex)
			{
				Monitor.sErrMsg("Application Error: " + ex.Message);
				return false;
			}
			finally
			{
				dbConn.Dispose();
			}
		}
		#endregion

	}
	#endregion
}
